﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using System.Data;
using System.Collections;
using OA.Model;
using OA.DBUtility;
using System.Data.SqlClient;

namespace OA.Manager
{
    public class UserInfoManager
    {
        private SqlService<UserInfo> _sqlService = null;

        public UserInfoManager()
        {
            _sqlService = new SqlService<UserInfo>();
        }

        public bool Delete(string filter)
        {
            try
            {
                string sqltxt = "DELETE FROM dbo.UserInfo WHERE 1=1" + filter;

                return _sqlService.ExecuteNonQuery(sqltxt) > 0;
            }
            catch (Exception ex)
            {
                //MoziLog.GetLogger("MoziPrintServer").LogError("错误方法为：{0}，失败原因：{1}", "Delete", ex.Message);
                return false;
            }
        }

        public DataSet Get(string search)
        {
            try
            {
                string sqltext = "SELECT * From UserInfo WHERE 1=1 " + search;
                List<UserInfo> list = new List<UserInfo>();

                DataSet ds = _sqlService.ExecuteDataSet(sqltext);

                return ds;
            }
            catch (Exception ex)
            {
                //MoziLog.GetLogger("MoziPrintServer").LogError("错误方法为：{0}，失败原因：{1}", "Get", ex.Message);
                return null;
            }
        }

        public bool Insert(UserInfo userinfo)
        {
            try
            {
                string sqltxt = "INSERT INTO  dbo.UserInfo" +
                                "(UserName,Password,CreateTime,Status)" +
                                "VALUES ('" + userinfo.UserName + "','" + userinfo.Password + "','" +
                                userinfo.CreateTime.ToString() + "','" + userinfo.Status.ToString() + "')";

                return _sqlService.ExecuteNonQuery(sqltxt) > 0;
            }
            catch (Exception ex)
            {
                //MoziLog.GetLogger("MoziPrintServer").LogError("错误方法为：{0}，失败原因：{1}", "Insert", ex.Message);
                return false;
            }
        }

        public bool Update(UserInfo userinfo)
        {
            try
            {
                string sqltxt = "update UserInfo set Password='" + userinfo.Password + "'where UserName ='" +
                                userinfo.UserName + "'";

                return _sqlService.ExecuteNonQuery(sqltxt) > 0;
            }
            catch (Exception ex)
            {
                //MoziLog.GetLogger("MoziPrintServer").LogError("错误方法为：{0}，失败原因：{1}", "Update", ex.Message);
                return false;
            }
        }
        #region 用户操作

        /// <summary>
        /// 用户是否存在该
        /// </summary>
        public bool UserExists(string UserName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from UserInfo");
            strSql.Append(" where UserName='" + UserName + "'");
            //SqlParameter[] parameters = {
            //        new SqlParameter("@UserName", SqlDbType.NVarChar,128)};
            //parameters[0].Value = UserName;

            object obj = _sqlService.ExecuteScalar(strSql.ToString());
            if (obj != null)
            {
                int count;
                if(int.TryParse(obj.ToString(),out count))
                {
                    return count >0;
                }
            }
            return false;
        }

        /// <summary>
        /// 创建一个新用户
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int CreateUser(UserInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into UserInfo(");
            strSql.Append("UserName,Password,Question,Answer,CreateTime,LastLoginTime,Status)");
            strSql.Append(" values (");
            strSql.Append("@UserName,@Password,@Question,@Answer,@CreateTime,@LastLoginTime,@Status)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                                            new SqlParameter("@UserName", SqlDbType.VarChar, 100),
                                            new SqlParameter("@Password", SqlDbType.NVarChar, 50),
                                            new SqlParameter("@Question", SqlDbType.NVarChar, 100),
                                            new SqlParameter("@Answer", SqlDbType.NVarChar, 100),
                                            new SqlParameter("@CreateTime", SqlDbType.DateTime),
                                            new SqlParameter("@LastLoginTime", SqlDbType.DateTime),
                                            new SqlParameter("@Status", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = model.UserName;
            parameters[1].Value = model.Password;
            parameters[2].Value = model.Question;
            parameters[3].Value = model.Answer;
            parameters[4].Value = model.CreateTime;
            parameters[5].Value = model.LastLoginTime;
            parameters[6].Value = model.Status;
            SqlService sqlService = new SqlService();
            object obj = sqlService.ExecuteScalar(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新用户基本信息
        /// </summary>
        /// <param name="mode"></param>
        /// <returns></returns>
        public int UpdateUser(UserInfo model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update UserInfo set ");
            strSql.Append("UserName=@UserName,");
            strSql.Append("Password=@Password,");
            strSql.Append("Question=@Question,");
            strSql.Append("Answer=@Answer,");
            strSql.Append("CreateTime=@CreateTime,");
            strSql.Append("LastLoginTime=@LastLoginTime,");
            strSql.Append("Status=@Status");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
                                            new SqlParameter("@UserName", SqlDbType.VarChar, 100),
                                            new SqlParameter("@Password", SqlDbType.NVarChar, 50),
                                            new SqlParameter("@Question", SqlDbType.NVarChar, 100),
                                            new SqlParameter("@Answer", SqlDbType.NVarChar, 100),
                                            new SqlParameter("@CreateTime", SqlDbType.DateTime),
                                            new SqlParameter("@LastLoginTime", SqlDbType.DateTime),
                                            new SqlParameter("@Status", SqlDbType.Int, 4),
                                            new SqlParameter("@Id", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = model.UserName;
            parameters[1].Value = model.Password;
            parameters[2].Value = model.Question;
            parameters[3].Value = model.Answer;
            parameters[4].Value = model.CreateTime;
            parameters[5].Value = model.LastLoginTime;
            parameters[6].Value = model.Status;
            parameters[7].Value = model.Id;

            return _sqlService.ExecuteNonQuery(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 用户登录检测
        /// </summary>
        public bool CheckLogin(string userName, string pwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from UserInfo");
            strSql.Append(" where UserName=? and Password=?");
            SqlParameter[] parameters = {
                                             new SqlParameter("@UserName", SqlDbType.NVarChar, 128),
                                             new SqlParameter("@Password", SqlDbType.NVarChar, 128)
                                         };
            parameters[0].Value = userName;
            parameters[1].Value = pwd;
            DataSet ds = _sqlService.ExecuteDataSet(strSql.ToString(), parameters);
            if (ds == null)
                return false;
            return ds.Tables[0].Rows.Count > 0;
        }

        /// <summary>
        /// 更新用户登录时间
        /// </summary>
        /// <param name="id"></param>
        public void UpdateLoginTime(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update UserInfo set LastLoginTime=@LastLoginTime  where Id=@UserID");
            SqlParameter[] parameters = {
                                             new SqlParameter("@LastLoginTime", SqlDbType.DateTime),
                                             new SqlParameter("@UserID", SqlDbType.Int, 4)
                                         };

            parameters[0].Value = DateTime.Now;
            parameters[1].Value = id;
            _sqlService.ExecuteNonQuery(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 判断用户原密码是否正确
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <param name="pwd">原密码</param>
        /// <returns></returns>
        public bool VerifyPassword(int id, string pwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from UserInfo");
            strSql.Append(" where Id=@UserID   and Password=@Password");
            SqlParameter[] parameters = {
                                             new SqlParameter("@UserID", SqlDbType.Int, 4),
                                             new SqlParameter("@Password", SqlDbType.NVarChar, 128)
                                         };
            parameters[0].Value = id;
            parameters[1].Value = pwd;
            DataSet ds = _sqlService.ExecuteDataSet(strSql.ToString(), parameters);
            if (ds == null)
                return false;
            return ds.Tables[0].Rows.Count > 0;
        }

        /// <summary>
        /// 更改用户密码
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <param name="pwd">新密码</param>
        /// <returns></returns>
        public bool ChangePassword(int id, string pwd)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update UserInfo set ");
            strSql.Append("Password=@Password");
            strSql.Append(" where Id=@UserID");

            SqlParameter[] parameters = {

                                             new SqlParameter("@Password", SqlDbType.NVarChar, 128),
                                             new SqlParameter("@UserID", SqlDbType.Int, 4)
                                         };

            parameters[0].Value = pwd;
            parameters[1].Value = id;
            return _sqlService.ExecuteNonQuery(strSql.ToString(), parameters) >= 1;
        }

        /// <summary>
        /// 更新安全信息
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <param name="question">问题</param>
        /// <param name="answer">答案</param>
        /// <returns></returns>
        public bool ChangeSecureInfo(int id, string question, string answer)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update UserInfo set ");
            strSql.Append("Question=@Question,Answer=@Answer");
            strSql.Append(" where Id=@UserID");

            SqlParameter[] parameters = {

                                             new SqlParameter("@Question", SqlDbType.NVarChar, 100),
                                             new SqlParameter("@Answer", SqlDbType.NVarChar, 100),
                                             new SqlParameter("@UserID", SqlDbType.Int, 4)
                                         };

            parameters[0].Value = question;
            parameters[1].Value = answer;
            parameters[2].Value = id;
            return _sqlService.ExecuteNonQuery(strSql.ToString(), parameters) >= 1;
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="userID">用户ID</param>
        public bool DeleteUser(int userID)
        {

            SqlParameter[] parameters = {
                                             new SqlParameter("@UserID", SqlDbType.Int, 4)
                                         };
            parameters[0].Value = userID;

            ArrayList sqlList = new ArrayList();

            try
            {
                sqlList.Add("delete RoleAuthorityList where UserID=" + parameters[0].Value);
                sqlList.Add("delete t_UserRoles where UserID=" + parameters[0].Value);
                sqlList.Add("delete UserInfo where Id=" + parameters[0].Value);


                _sqlService.ExecuteSqlTran(sqlList);
                return true; //删除成功
            }
            catch (Exception ex)
            {
               // MoziLog.GetLogger("MoziPrintServer").LogError("错误方法为：{0}，失败原因：{1}", "DeleteUser", ex.Message);
                return false; //删除失败
            }

        }

        ///// <summary>
        ///// 获得数据列表
        ///// </summary>
        //public DataSet GetUserRoleGroupList(string Columns, string Search, int PageIndex, int PageSize,
        //                                    ref int PageCount, ref int TotalCnt)
        //{
        //    try
        //    {
        //        //string sqltext = "{call Pager(?,?,?,?,?,?,?,?)}";
        //        List<SqlParameter> Params = new List<SqlParameter>();

        //        SqlParameter WhichTable = new SqlParameter();
        //        WhichTable.ParameterName = "@WhichTable";
        //        WhichTable.SqlDbType = SqlDbType.NVarChar;
        //        WhichTable.Direction = ParameterDirection.Input;
        //        WhichTable.Value = "ViewUserRoleGroup"; //
        //        Params.Add(WhichTable);

        //        SqlParameter KeyCol = new SqlParameter();
        //        KeyCol.Value = "ID";
        //        KeyCol.ParameterName = "@KeyCol";
        //        KeyCol.Direction = ParameterDirection.Input;
        //        KeyCol.SqlDbType = SqlDbType.NVarChar;
        //        Params.Add(KeyCol);

        //        SqlParameter Column = new SqlParameter("@Columns", SqlDbType.NVarChar, 50);
        //        Column.Value = Columns;
        //        Column.Direction = ParameterDirection.Input;
        //        Params.Add(Column);

        //        SqlParameter OrderStr = new SqlParameter("@OrderStr", SqlDbType.NVarChar, 1000);
        //        OrderStr.Value = Search;
        //        OrderStr.Direction = ParameterDirection.Input;
        //        Params.Add(OrderStr);

        //        SqlParameter PageNo = new SqlParameter("@PageNo", SqlDbType.Int, 4);
        //        PageNo.Value = PageIndex;
        //        PageNo.Direction = ParameterDirection.Input;
        //        Params.Add(PageNo);

        //        SqlParameter PgSize = new SqlParameter("@PageSize", SqlDbType.Int, 4);
        //        PgSize.Direction = ParameterDirection.Input;
        //        PgSize.Value = PageSize;
        //        Params.Add(PgSize);


        //        SqlParameter pagecount = new SqlParameter();
        //        pagecount.ParameterName = "@PageCount";
        //        pagecount.SqlDbType = SqlDbType.Int;
        //        pagecount.Direction = ParameterDirection.Output;
        //        Params.Add(pagecount);

        //        SqlParameter recordscnt = new SqlParameter();
        //        recordscnt.ParameterName = "@Cnt";
        //        recordscnt.SqlDbType = SqlDbType.Int;
        //        recordscnt.Direction = ParameterDirection.Output;
        //        Params.Add(recordscnt);

        //        #region


        //        DataSet ds = _sqlService.ExecuteDataSetByProcedure("Pager", Params.ToArray());
        //        PageCount = Convert.ToInt32(pagecount.Value);
        //        TotalCnt = Convert.ToInt32(recordscnt.Value);

        //        #endregion

        //        return ds;

        //    }
        //    catch (Exception ex)
        //    {
        //        //MoziLog.GetLogger("MoziPrintServer").LogError("错误方法为：{0}，失败原因：{1}", "GetUserRoleGroupList", ex.Message);
        //        return null; //删除失败
        //    }
        //}

        /// <summary>
        /// 读取角色名称
        /// </summary>
        /// <param name="roleid"></param>
        /// <returns></returns>
        public object GetRoleName(int roleid)
        {

            return _sqlService.ExecuteNonQuery("select RoleName from Roles where RoleID=" + roleid);
        }


        #endregion

        #region 角色操作

        /// <summary>
        /// 添加一用户角色
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="roleID">角色ID</param>
        /// <returns></returns>
        public bool addUserRole(int userID, int roleID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into t_UserRoles(");
            strSql.Append("UserID,RoleID)");
            strSql.Append(" values (");
            strSql.Append("@UserID,@RoleID)");
            SqlParameter[] parameters = {
                                             new SqlParameter("@UserID", SqlDbType.Int, 4),
                                             new SqlParameter("@RoleID", SqlDbType.Int, 4)
                                         };
            parameters[0].Value = userID;
            parameters[1].Value = roleID;

            return _sqlService.ExecuteNonQuery(strSql.ToString(), parameters) >= 1;
        }

        /// <summary>
        /// 批量添加用户角色
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool addUserRole(ArrayList list)
        {
            ArrayList sqlList = new ArrayList();

            for (int i = 0; i < list.Count; i++)
            {
                string[] val = list[i].ToString().Split(',');
                sqlList.Add("insert into dbo.UserRole(UserID,RoleID) values (" + val[0] + "," + val[1] + ")");
            }

            try
            {
                //SqlHelper.ExecuteSqlTran(SQLList);

                _sqlService.ExecuteSqlTran(sqlList);
                return true;
            }
            catch (Exception ex)
            {
                //MoziLog.GetLogger("MoziPrintServer").LogError("错误方法为：{0}，失败原因：{1}", "addUserRole", ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 删除一用户角色
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="roleID">角色ID</param>
        /// <returns></returns>
        public bool DeleteUserRole(int userID, int roleID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete dbo.UserRole where UserID=@UserID  and RoleID=@RoleID");
            SqlParameter[] parameters = {
                                             new SqlParameter("@UserID", SqlDbType.Int, 4),
                                             new SqlParameter("@RoleID", SqlDbType.Int, 4)
                                         };
            parameters[0].Value = userID;
            parameters[0].Value = roleID;

            return _sqlService == null || _sqlService.ExecuteNonQuery(strSql.ToString(), parameters) < 1;
        }

        /// <summary>
        /// 批量删除用户角色
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool DeleteUserRole(ArrayList list)
        {
            ArrayList sqlList = new ArrayList();

            for (int i = 0; i < list.Count; i++)
            {
                string[] val = list[i].ToString().Split(',');
                sqlList.Add("delete UserRole where UserID=" + val[0] + " and RoleID=" + val[1]);
            }

            try
            {
                //SqlHelper.ExecuteSqlTran(SQLList);

                _sqlService.ExecuteSqlTran(sqlList);
                return true;
            }
            catch (Exception ex)
            {
                //MoziLog.GetLogger("MoziPrintServer").LogError("错误方法为：{0}，失败原因：{1}", "DeleteUserRole", ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 读取用户角色列表
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataSet GetUserRole(int userID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT dbo.UserInfo.*,dbo.UserRole.RoleID ,dbo.Roles.RoleName ");
            strSql.Append("FROM  dbo.UserInfo left JOIN ");
            strSql.Append(
                "dbo.UserRole ON dbo.UserInfo.ID = dbo.UserRole.UserID left JOIN dbo.Roles ON dbo.UserRole.RoleID = dbo.Roles.Id  ");

            return
                _sqlService.ExecuteDataSet(strSql + "where dbo.UserInfo.ID=" +
                                             userID.ToString(CultureInfo.InvariantCulture));
        }

        /// <summary>
        /// 读取用户角色列表
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public ArrayList GetUserRoleArray(int userID)
        {
            ArrayList ret = new ArrayList();
            DataSet rds = GetUserRole(userID);
            if (rds.Tables[0].Rows.Count != 0)
            {
                for (int i = 0; i < rds.Tables[0].Rows.Count; i++)
                {
                    ret.Add(rds.Tables[0].Rows[i]["RoleID"] + "," + rds.Tables[0].Rows[i]["RoleName"]);
                }
            }
            return ret;
        }

        #endregion


        /// <summary>
        /// 根据ID得到用户对象实体
        /// </summary>
        public ViewUserRoleGroupInfo GetUserModel(string filter)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from dbo.ViewUserRoleGroup where 1=1");
            strSql.Append(filter);
            ViewUserRoleGroupInfo model = new ViewUserRoleGroupInfo();
            DataSet ds = _sqlService.ExecuteDataSet(strSql.ToString());
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["UserID"].ToString() != "")
                {
                    model.UserID = int.Parse(ds.Tables[0].Rows[0]["UserID"].ToString());
                }
                model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                model.Password = ds.Tables[0].Rows[0]["Password"].ToString();

                model.Question = ds.Tables[0].Rows[0]["Question"].ToString();
                model.Answer = ds.Tables[0].Rows[0]["Answer"].ToString();


                if (ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = DateTime.Parse(ds.Tables[0].Rows[0]["LastLoginTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
                }
                ArrayList ret = new ArrayList();
                if (ds.Tables[0].Rows.Count != 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        ret.Add(ds.Tables[0].Rows[i]["RoleID"].ToString() + "," +
                                ds.Tables[0].Rows[i]["RoleName"].ToString());
                    }
                }
                model.RoleID = ret;
                int employeeId = 0;
                int.TryParse(ds.Tables[0].Rows[0]["EmployeeId"].ToString(), out employeeId);
                model.EmployeeId = employeeId;
                return model;
            }
            return null;
        }

    }
}
